跳到主要内容

Postgress 使用笔记

在使用 Docker 部署的 PostgreSQL 中，导出数据的过程与普通 PostgreSQL 数据库类似，但需要通过 Docker 容器来操作。以下是具体步骤：

1. 确认 PostgreSQL 容器正在运行​

首先，确保 PostgreSQL 容器正在运行。

运行以下命令查看容器列表：

docker ps

找到 PostgreSQL 容器的 CONTAINER ID 或 NAME，例如 postgres-container。

2. 进入 PostgreSQL 容器​

使用 docker exec 命令进入 PostgreSQL 容器的终端：

docker exec -it <container_name_or_id> bash

例如：

docker exec -it postgres-container bash

进入容器后，你可以使用 PostgreSQL 提供的命令行工具（如 pg_dump）进行数据导出。

3. 使用 pg_dump 导出数据​

进入容器后，使用以下命令导出 PostgreSQL 数据。

导出单个数据库​

使用 pg_dump 导出特定数据库：

pg_dump -U <用户名> -d <数据库名> -F c -f /tmp/<导出文件名>.dump

示例：

pg_dump -U postgres -d mydb -F c -f /tmp/mydb.dump
  • -U：指定 PostgreSQL 用户名（如 postgres）。
  • -d：指定要导出的数据库名（如 mydb）。
  • -F c：指定导出格式为自定义格式（c）。
  • -f：指定导出文件的路径（如 /tmp/mydb.dump）。

导出为 SQL 脚本​

如果需要导出为 SQL 文件：

pg_dump -U postgres -d mydb -F p -f /tmp/mydb.sql

导出整个数据库集群​

如果需要导出容器中所有的数据库，可以使用 pg_dumpall：

pg_dumpall -U postgres -f /tmp/all_databases.sql

4. 将导出的文件拷贝到主机​

导出完成后，文件会保存在容器内部。需要将文件拷贝到主机上。

使用 docker cp 命令：

docker cp <container_name_or_id>:/tmp/<导出文件名>.dump <主机路径>

例如：

docker cp postgres-container:/tmp/mydb.dump /home/user/mydb.dump

5. 退出容器​

完成操作后，可以退出容器：

exit

6. 直接从主机导出数据（可选）​

如果不想进入容器，也可以直接在主机上运行 docker exec 命令来导出数据。

导出单个数据库​

docker exec -i <container_name_or_id> pg_dump -U <用户名> -d <数据库名> -F c > <主机文件路径>

示例：

docker exec -i postgres-container pg_dump -U postgres -d mydb -F c > /home/user/mydb.dump

导出为 SQL 脚本​

docker exec -i postgres-container pg_dump -U postgres -d mydb -F p > /home/user/mydb.sql

导出整个数据库集群​

docker exec -i postgres-container pg_dumpall -U postgres > /home/user/all_databases.sql

7. 导出表为 CSV（可选）​

如果只需要导出某张表的数据为 CSV，可以使用以下命令：

docker exec -i <container_name_or_id> psql -U <用户名> -d <数据库名> -c "\COPY <表名> TO '/tmp/<表名>.csv' CSV HEADER"

然后将 CSV 文件从容器拷贝到主机：

docker cp <container_name_or_id>:/tmp/<表名>.csv <主机路径>

总结​

  1. 登录容器：使用 docker exec 进入容器。
  2. 使用 pg_dump 或 pg_dumpall：导出数据库或整个集群。
  3. 拷贝文件到主机：使用 docker cp 将容器内的导出文件复制到主机。
  4. 直接导出到主机（可选）：使用 docker exec 将数据直接导出到主机文件系统。

如果有其他问题，请随时告诉我！

以下是 PostgreSQL 的登录操作及常用查询命令的详细说明，包括如何登录数据库、基础操作和常用 SQL 查询。

1. 登录 PostgreSQL​

1.1 登录到 PostgreSQL​

在终端中使用 psql 命令登录 PostgreSQL 数据库。

本地登录​

psql -U <用户名> -d <数据库名>

示例：

psql -U postgres -d mydb
  • -U <用户名>：指定登录的数据库用户（如 postgres）。
  • -d <数据库名>：指定要连接的数据库名称。

如果 PostgreSQL运行在不同的主机或端口上：

psql -h <主机地址> -p <端口号> -U <用户名> -d <数据库名>

示例：

psql -h 127.0.0.1 -p 5432 -U postgres -d mydb

1.2 登录到 PostgreSQL 容器​

如果 PostgreSQL 运行在 Docker 容器中，可以通过以下方式登录：

  1. 进入容器：

    docker exec -it <container_name_or_id> bash

  2. 登录 PostgreSQL：

    psql -U <用户名> -d <数据库名>

或者直接运行：

docker exec -it <container_name_or_id> psql -U <用户名> -d <数据库名>

2. 常用命令​

2.1 数据库相关操作​

查看所有数据库​

\l

切换数据库​

\c <数据库名>

示例：

\c mydb

创建数据库​

CREATE DATABASE <数据库名>;

示例：

CREATE DATABASE mydb;

删除数据库​

DROP DATABASE <数据库名>;

示例：

DROP DATABASE mydb;

2.2 用户相关操作​

查看所有用户​

\du

创建用户​

CREATE USER <用户名> WITH PASSWORD '<密码>';

示例：

CREATE USER myuser WITH PASSWORD 'mypassword';

删除用户​

DROP USER <用户名>;

示例：

DROP USER myuser;

给用户赋予权限​

GRANT ALL PRIVILEGES ON DATABASE <数据库名> TO <用户名>;

示例：

GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;

2.3 表相关操作​

查看当前数据库中的所有表​

\dt

创建表​

CREATE TABLE <表名> (
    <列名1> <数据类型> [约束],
    <列名2> <数据类型> [约束],
    ...
);

示例：

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    age INT,
    email VARCHAR(100)
);

删除表​

DROP TABLE <表名>;

示例：

DROP TABLE users;

查看表结构​

\d <表名>

示例：

\d users

2.4 插入、查询、更新和删除数据​

插入数据​

INSERT INTO <表名> (<列名1>, <列名2>, ...) VALUES (<值1>, <值2>, ...);

示例：

INSERT INTO users (name, age, email) VALUES ('Alice', 25, 'alice@example.com');

查询数据​

SELECT <列名1>, <列名2>, ... FROM <表名> WHERE <条件>;

示例：

SELECT * FROM users;
SELECT name, age FROM users WHERE age > 20;

更新数据​

UPDATE <表名> SET <列名1>=<值1>, <列名2>=<值2>, ... WHERE <条件>;

示例：

UPDATE users SET age = 26 WHERE name = 'Alice';

删除数据​

DELETE FROM <表名> WHERE <条件>;

示例：

DELETE FROM users WHERE age < 18;

2.5 备份与还原​

使用 pg_dump 备份数据库​

pg_dump -U <用户名> -d <数据库名> -F c -f <备份文件路径>

示例：

pg_dump -U postgres -d mydb -F c -f /path/to/backup.dump

使用 pg_restore 还原数据库​

pg_restore -U <用户名> -d <数据库名> -F c <备份文件路径>

示例：

pg_restore -U postgres -d mydb -F c /path/to/backup.dump

使用 pg_dumpall 备份所有数据库​

pg_dumpall -U <用户名> > <备份文件路径>

示例：

pg_dumpall -U postgres > /path/to/all_databases.sql

使用 psql 恢复所有数据库​

psql -U <用户名> -f <备份文件路径>

示例：

psql -U postgres -f /path/to/all_databases.sql

2.6 退出 PostgreSQL​

在 psql 命令行中，输入以下命令退出：

\q

2.7 其他常用命令​

查看当前连接的数据库名​

SELECT current_database();

查看当前用户​

SELECT current_user;

查看数据库版本​

SELECT version();

查看表中的前几行数据​

SELECT * FROM <表名> LIMIT <行数>;

示例：

SELECT * FROM users LIMIT 10;

总结​

  • 登录数据库：使用 psql 或 docker exec 登录。
  • 常用命令：包括创建/删除数据库、用户管理、表操作、数据查询等。
  • 备份与还原：使用 pg_dump 和 pg_restore 进行数据库导入导出。

如果有其他需求或需要更详细的说明，请随时告诉我！

这个错误表明 PostgreSQL 客户端无法连接到服务器，因为它找不到运行中的 PostgreSQL 服务或对应的套接字文件。以下是解决问题的步骤：

1. 确认 PostgreSQL 是否已安装​

  • 在 Termux 中运行以下命令，检查 PostgreSQL 是否已正确安装： 
    pkg install postgresql
  • 如果 PostgreSQL 未安装，安装后再继续操作。

2. 初始化数据库​

  • 如果 PostgreSQL 是新安装的，你需要先初始化数据库： 
    initdb $PREFIX/var/lib/postgresql
  • 这一步会创建一个用于存储数据的 PostgreSQL 数据库目录。

3. 启动 PostgreSQL 服务​

  • PostgreSQL 需要一个服务来运行，确保它已启动： 
    pg_ctl -D $PREFIX/var/lib/postgresql start
  • 如果启动成功，应该会显示类似以下内容： 
    server started
  • 如果出现错误，请检查日志文件（通常位于 $PREFIX/var/lib/postgresql/pg_log）。

4. 检查套接字文件路径​

  • 默认情况下，PostgreSQL 的套接字文件会存储在 /tmp 或其他指定目录中。
  • Termux 的 PostgreSQL 使用 $PREFIX/tmp 作为套接字目录，因此需要确保路径正确。
  • 如果路径不正确，可以通过以下方式指定套接字路径： 
    psql -h $PREFIX/tmp

5. 验证服务是否运行​

  • 使用以下命令检查 PostgreSQL 服务是否正在运行： 
    pg_ctl status
  • 如果服务未运行，尝试重新启动： 
    pg_ctl -D $PREFIX/var/lib/postgresql restart

6. 检查端口和配置​

  • 确保 PostgreSQL 使用正确的端口（默认是 5432）。你可以通过编辑配置文件来检查： 
    nano $PREFIX/var/lib/postgresql/postgresql.conf
  • 确保以下设置正确： 
    port = 5432
    unix_socket_directories = '/data/data/com.termux/files/usr/tmp'

7. 查看日志文件​

  • 如果问题仍然存在，查看 PostgreSQL 的日志文件以获取更多信息： 
    cat $PREFIX/var/lib/postgresql/pg_log/*.log

8. 重建数据库目录（如果必要）​

  • 如果数据库目录损坏，可以尝试重新初始化： 
    rm -rf $PREFIX/var/lib/postgresql
    initdb $PREFIX/var/lib/postgresql
    pg_ctl -D $PREFIX/var/lib/postgresql start

9. 尝试连接​

  • 一旦服务启动成功，尝试连接： 
    psql -h $PREFIX/tmp -p 5432

总结​

这个错误通常是由于 PostgreSQL 服务未启动或套接字路径配置错误引起的。通过以上步骤，你应该能够解决问题。如果问题仍然存在，请提供日志文件内容以便进一步排查。 PostgreSQL 提供了丰富的命令行工具，用于数据库的管理、备份、恢复、调试等操作。以下是 PostgreSQL 中常见的命令行工具及其使用说明：

1. initdb​

  • 功能：初始化数据库集群（创建 PostgreSQL 数据库的存储目录）。

  • 常用语法：

    initdb [OPTION]... [DATADIR]

  • 常用选项：

    • -D DATADIR：指定数据库的数据目录。
    • --username=USERNAME：指定超级用户名称（默认是当前用户）。
    • --auth=METHOD：指定本地用户的认证方法（如 trust, password, md5 等）。
    • --no-locale：不使用区域设置，默认使用 C 区域。
    • --encoding=ENCODING：指定数据库编码（如 UTF8）。

  • 示例：

    initdb -D /var/lib/postgresql/data --encoding=UTF8 --auth=md5

2. pg_ctl​

  • 功能：用于启动、停止和检查 PostgreSQL 数据库服务器的状态。

  • 常用语法：

    pg_ctl [OPTION]... {start|stop|restart|reload|status}

  • 常用选项：

    • -D DATADIR：指定数据库的数据目录。
    • -l FILENAME：指定日志文件。
    • -w：等待操作完成。
    • -m MODE：指定停止模式（smart, fast, immediate）。

  • 示例：

    pg_ctl -D /var/lib/postgresql/data start
    pg_ctl -D /var/lib/postgresql/data stop -m fast
    pg_ctl -D /var/lib/postgresql/data status

3. psql​

  • 功能：PostgreSQL 的交互式命令行客户端，用于执行 SQL 查询和管理数据库。

  • 常用语法：

    psql [OPTION]... [DBNAME [USERNAME]]

  • 常用选项：

    • -h HOSTNAME：指定数据库主机。
    • -p PORT：指定数据库端口。
    • -U USERNAME：指定连接的用户名。
    • -d DBNAME：指定连接的数据库名称。
    • -f FILENAME：从文件中读取 SQL 脚本并执行。
    • -c COMMAND：执行单个 SQL 命令后退出。

  • 示例：

    psql -U postgres -d mydb
    psql -h localhost -p 5432 -U postgres -c "SELECT * FROM mytable;"
    psql -f script.sql

4. pg_dump​

  • 功能：备份单个数据库为 SQL 脚本或二进制格式。

  • 常用语法：

    pg_dump [OPTION]... [DBNAME]

  • 常用选项：

    • -U USERNAME：指定连接的用户名。
    • -f FILENAME：将输出保存到文件。
    • -F FORMAT：指定输出格式（p 为纯文本，c 为自定义格式，d 为目录格式，t 为 tar 格式）。
    • -t TABLE：仅导出指定的表。
    • --data-only：仅导出数据，不导出表结构。
    • --schema-only：仅导出表结构，不导出数据。

  • 示例：

    pg_dump -U postgres -d mydb -f backup.sql
    pg_dump -U postgres -d mydb -F c -f backup.dump
    pg_dump -U postgres -d mydb -t mytable -f table_backup.sql

5. pg_restore​

  • 功能：从 pg_dump 的自定义格式或目录格式备份中恢复数据。

  • 常用语法：

    pg_restore [OPTION]... [FILENAME]

  • 常用选项：

    • -d DBNAME：指定要恢复到的数据库。
    • -C：在恢复前创建数据库。
    • -j NJOBS：指定并行恢复的作业数。
    • -l：列出备份文件中的内容。
    • -t TABLE：仅恢复指定的表。

  • 示例：

    pg_restore -U postgres -d mydb backup.dump
    pg_restore -U postgres -C -d postgres backup.dump
    pg_restore -U postgres -d mydb -t mytable table_backup.dump

6. createdb​

  • 功能：创建一个新的数据库。

  • 常用语法：

    createdb [OPTION]... [DBNAME]

  • 常用选项：

    • -U USERNAME：指定连接的用户名。
    • -E ENCODING：指定数据库的编码。
    • --locale=LOCALE：指定区域设置。
    • -T TEMPLATE：指定数据库模板。

  • 示例：

    createdb -U postgres mydb
    createdb -U postgres -E UTF8 --locale=en_US.UTF-8 mydb

7. dropdb​

  • 功能：删除一个数据库。

  • 常用语法：

    dropdb [OPTION]... DBNAME

  • 常用选项：

    • -U USERNAME：指定连接的用户名。
    • -i：交互模式，删除前提示确认。

  • 示例：

    dropdb -U postgres mydb

8. pg_basebackup​

  • 功能：用于创建整个数据库集群的基础备份。

  • 常用语法：

    pg_basebackup [OPTION]...

  • 常用选项：

    • -D DIRECTORY：指定备份存储目录。
    • -F FORMAT：指定备份格式（p 为纯文本，t 为 tar 格式）。
    • -X METHOD：指定 WAL 日志处理方式（fetch 或 stream）。
    • -z：启用压缩。
    • -P：显示进度条。

  • 示例：

    pg_basebackup -D /backup -F tar -z -P

9. pg_isready​

  • 功能：检查 PostgreSQL 数据库服务器是否可用。

  • 常用语法：

    pg_isready [OPTION]...

  • 常用选项：

    • -h HOSTNAME：指定数据库主机。
    • -p PORT：指定数据库端口。
    • -U USERNAME：指定连接的用户名。

  • 示例：

    pg_isready -h localhost -p 5432 -U postgres

10. vacuumdb​

  • 功能：清理数据库，回收无用空间并更新统计信息。

  • 常用语法：

    vacuumdb [OPTION]... [DBNAME]

  • 常用选项：

    • -z：分析数据库并更新统计信息。
    • -f：强制执行全量清理。
    • -t TABLE：仅清理指定表。

  • 示例：

    vacuumdb -U postgres -z mydb

11. reindexdb​

  • 功能：重新构建数据库索引。

  • 常用语法：

    reindexdb [OPTION]... [DBNAME]

  • 常用选项：

    • -t TABLE：重新索引指定表。
    • --system：重新索引系统表。

  • 示例：

    reindexdb -U postgres mydb

12. pg_dumpall​

  • 功能：备份整个数据库集群（包括所有数据库和全局对象，如角色和权限）。

  • 常用语法：

    pg_dumpall [OPTION]...

  • 常用选项：

    • -f FILENAME：将输出保存到文件。
    • --globals-only：仅备份全局对象（如角色）。

  • 示例：

    pg_dumpall -U postgres -f all_databases.sql

13. pg_config​

  • 功能：显示 PostgreSQL 的编译和安装信息。
  • 常用语法： 
    pg_config
  • 示例： 
    pg_config

总结​

以上列出了 PostgreSQL 的主要命令行工具及其常用选项和使用示例。这些工具涵盖了数据库初始化、备份、恢复、清理、优化、监控等各个方面，是管理 PostgreSQL 数据库的核心工具集。如果需要更详细的帮助，可以通过 --help 查看每个命令的完整选项说明。

在 PostgreSQL 中，配置远程登录密码需要进行以下几个步骤，包括修改配置文件、创建或修改用户密码，以及确保远程访问权限正确设置。以下是详细的配置步骤：

1. 修改 PostgreSQL 配置以允许远程连接​

默认情况下，PostgreSQL 只允许本地连接。如果需要远程连接，需要修改 postgresql.conf 和 pg_hba.conf 文件。

1.1 修改 postgresql.conf 文件​

  1. 找到 postgresql.conf 文件，通常位于 PostgreSQL 数据目录下（例如 /var/lib/pgsql/data/ 或 /etc/postgresql/<version>/main/）。

  2. 打开文件：

    nano /path/to/postgresql.conf

  3. 找到以下配置项：

    #listen_addresses = 'localhost'

  4. 修改为：

    listen_addresses = '*'
    • * 表示监听所有 IP 地址。如果只允许特定 IP，可以用逗号分隔指定 IP（如 listen_addresses = '192.168.1.100'）。

  5. 保存并退出。

1.2 修改 pg_hba.conf 文件​

  1. 找到 pg_hba.conf 文件，通常与 postgresql.conf 文件位于同一目录。

  2. 打开文件：

    nano /path/to/pg_hba.conf

  3. 添加以下内容到文件的末尾：

    # Allow remote connections with password authentication
    host    all             all             0.0.0.0/0               md5
    • host：表示允许远程连接。
    • all：表示允许连接所有数据库和所有用户。
    • 0.0.0.0/0：表示允许所有 IP 地址访问。如果需要限制特定 IP 段，可以替换为具体的范围（如 192.168.1.0/24）。
    • md5：表示使用 MD5 加密的密码验证。

  4. 保存并退出。

1.3 重启 PostgreSQL 服务​

完成修改后，需要重启 PostgreSQL 服务以使更改生效。

  • 如果使用 systemd 管理 PostgreSQL 服务：

    sudo systemctl restart postgresql

  • 如果使用 pg_ctl：

    pg_ctl restart -D /path/to/data

2. 设置 PostgreSQL 用户密码​

PostgreSQL 的用户（也叫角色）需要设置密码才能通过远程登录。

2.1 登录到 PostgreSQL​

  1. 使用本地超级用户登录 PostgreSQL：

    psql -U postgres

    或者：

    sudo -u postgres psql

  2. 确保你已经创建了一个用户。如果没有用户，可以通过以下命令创建一个新用户：

    CREATE USER username WITH PASSWORD 'password';
    ALTER USER username WITH LOGIN;

2.2 设置或修改用户密码​

  • 如果用户已经存在，可以通过以下命令设置或修改密码： 
    ALTER USER username WITH PASSWORD 'new_password';
  • 示例： 
    ALTER USER myuser WITH PASSWORD 'mypassword';

3. 测试远程连接​

完成配置后，可以通过以下方式测试远程连接。

3.1 使用 psql 测试​

  • 在远程机器上，使用以下命令连接到 PostgreSQL：

    psql -h <server_ip> -p 5432 -U username -d dbname
    • <server_ip>：PostgreSQL 服务器的 IP 地址。
    • 5432：PostgreSQL 的默认端口号。
    • username：登录的 PostgreSQL 用户名。
    • dbname：需要连接的数据库名称。

  • 如果连接成功，会进入 psql 的交互式命令行。

3.2 使用图形化工具测试​

可以使用图形化工具（如 pgAdmin, DBeaver, DataGrip）测试远程连接：

  1. 打开工具并创建一个新的连接。
  2. 填写以下信息：
    • Host：服务器的 IP 地址或域名。
    • Port：默认是 5432。
    • Database：需要连接的数据库名称。
    • Username：PostgreSQL 用户名。
    • Password：用户密码。
  3. 测试连接。

4. 常见问题排查​

4.1 防火墙问题​

确保服务器防火墙允许 PostgreSQL 的默认端口（5432）对外开放。如果防火墙阻止了流量，远程连接将失败。

检查防火墙规则​

  • 使用 ufw（Ubuntu/Debian 系统）：

    sudo ufw allow 5432/tcp
    sudo ufw reload

  • 使用 firewalld（CentOS/RHEL 系统）：

    sudo firewall-cmd --add-port=5432/tcp --permanent
    sudo firewall-cmd --reload

4.2 检查 PostgreSQL 服务是否运行​

确保 PostgreSQL 服务正在运行并监听正确的端口。

检查服务状态​

  • 使用 systemctl： 
    sudo systemctl status postgresql

检查监听端口​

  • 使用 netstat 或 ss： 
    netstat -tuln | grep 5432
    或 
    ss -tuln | grep 5432
    确保 PostgreSQL 正在监听 5432 端口。

4.3 检查日志文件​

如果配置完成后仍然无法连接，可以查看 PostgreSQL 的日志文件，排查问题原因。

日志文件位置​

  • 默认日志文件可能位于 /var/log/postgresql/ 或数据目录下的 pg_log/。
  • 打开日志文件查看错误信息： 
    tail -f /path/to/logfile

总结​

  1. 修改 postgresql.conf 和 pg_hba.conf 文件以允许远程连接。
  2. 设置或修改用户密码，并确保用户有登录权限。
  3. 确保防火墙和网络配置允许远程访问 PostgreSQL 的端口。
  4. 测试远程连接，并根据需要调整配置。
  5. 如果遇到问题，检查 PostgreSQL 的日志文件和服务状态。

通过以上步骤，你应该能够成功配置 PostgreSQL 允许远程登录并使用密码进行身份验证。